package com.df.springmvc.system.user.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.stereotype.Repository;

import com.common.bean.SQLInsertBean;
import com.common.bean.SQLUpdateBean;
import com.df.springmvc.supper.SupperDao;


@Repository
public class UserGroupDao extends SupperDao{
	private String message;
	public String getMessage(){
		return message;
	}
	public void setMessage(String message){
		this.message=message;
	}
	
	/**
	 * *
	 * @param sql
	 *            查询的SQL
	 * @param param_HashMap
	 *            查询参数
	 * @param i
	 *            从第几天开始
	 * @param j
	 *            显示多少条数据
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public ArrayList<Object> query(HashMap<String,String> map,Object[] attribute,int i,int j) throws Exception{
		// TODO Auto-generated method stub
		Session session=getSessionFactory().getCurrentSession();
		String sql="select '' as 'ck',ug.guid, ug.userGroupCode,ug.userGroupName,(select v.userName from  v_usergroupToUserName v where v.userGroupCode =ug.userGroupCode )  as userName ,(select v.roleName from  v_usergroup_roleToRoleName v where v.userGroupCode =ug.userGroupCode ) as roleName,ug.description from s_userGroup_info ug where 1=1 and ug.status = 1 ";
		if(map.get("userGroupCode") != null && !"".equals(map.get("userGroupCode"))){
			sql=sql + " and ug.userGroupCode LIKE :s  ";
		}
		if(map.get("userGroupName") != null && !"".equals(map.get("userGroupName"))){
			sql=sql + " and ug.userGroupName LIKE :userGroupName ";
		}
		sql = sql+" order by ug.createTime desc";
		SQLQuery query=session.createSQLQuery(sql);
		if(map.get("userGroupCode") != null && !"".equals(map.get("userGroupCode"))){
			query.setParameter("s","%" + map.get("userGroupCode") + "%");
		}
		if(map.get("userGroupName") != null && !"".equals(map.get("userGroupName"))){
			query.setParameter("userGroupName","%" + map.get("userGroupName") + "%");
		}
		query.setFirstResult(i);
		query.setMaxResults(j);
		return (ArrayList<Object>)query.list();
	}
	/**
	 * *
	 * 添加的时候验证重复
	 * @param map
	 * @return
	 */
	public Object queryVerify(HashMap<String,String> map){
		Session session=getSessionFactory().getCurrentSession();
		String sql="select 0 from s_userGroup_info where userGroupName=:userGroupName and STATUS = 1";
		SQLQuery query=session.createSQLQuery(sql);
		query.setString("userGroupName",map.get("userGroupName"));
		if(query.list().size() > 0){
			message="有重复的用户组名称，请重新输入！";
			return message;
		}else{
			Session session2=getSessionFactory().getCurrentSession();
			String sql2="select 0 from s_userGroup_info where userGroupCode=:userGroupCode and STATUS = 1";
			SQLQuery query2=session2.createSQLQuery(sql2);
			query2.setString("userGroupCode",map.get("userGroupCode"));
			if(query2.list().size() > 0){
				message="有重复的用户组编号，请重新输入！";
				return message;
			}
		}
		return null;
	}
	/**
	 * 修改的时候验重
	 * @param map
	 * @return
	 */
	public Object queryVerifyUpd(HashMap<String,String> map){
		Session session=getSessionFactory().getCurrentSession();
		String sql="select 0 from s_userGroup_info where userGroupName=:userGroupName and STATUS = 1 and guid not in (:gu)";
		SQLQuery query=session.createSQLQuery(sql);
		query.setString("userGroupName",map.get("userGroupName"));
		query.setString("gu",map.get("guid"));
		if(query.list().size() > 0){
			message="有重复的用户组名称，请重新输入！";
			return message;
		}
		return null;
	}
	
	public Object query(HashMap<String,String> map){
		// TODO Auto-generated method stub
		Session session=getSessionFactory().getCurrentSession();
		String sql="select * from s_users_info where 1=1 and STATUS = 1 and guid=:g";
		SQLQuery query=session.createSQLQuery(sql);
		query.setString("g",map.get("guid"));
		return query.list();
	}
	@Override
	public Object add(HashMap<String,Object> map) throws Exception{
		// TODO Auto-generated method stub
		SQLInsertBean insertBean=new SQLInsertBean("s_userGroup_info");
		HashMap<String,Object> columnMap=new HashMap<String,Object>(map);
		insertBean.setColumnMap(columnMap);
		Object o=excuteSqlBean(insertBean);
		return o;
	}
	
	@Override
	public Object upd(HashMap<String,Object> map) throws Exception{
		// TODO Auto-generated method stub
		HashMap<String,Object> tmp=new HashMap<String,Object>();
		tmp.put("guid",map.get("guid"));
		map.remove("userGroupCode");
		Object rs=excuteSqlBean(new SQLUpdateBean("s_userGroup_info",map,tmp){});
		return rs;
	}
	
	@Override
	public Object del(HashMap<String,Object> map) throws Exception{
		// TODO Auto-generated method stub
		String result = "fail";
		String result2 = "fail";
		String userGroupCode = (String) map.get("userGroupCode");
		map.remove("userGroupCode");
		HashMap<String, Object> tmp = new HashMap<String, Object>();
		tmp.put("guid", map.get("guid"));
		Object rs = excuteSqlBean(new SQLUpdateBean("s_userGroup_info", map, tmp) {});
		if("1".equals(rs)){
			ArrayList<Object> listQuery = (ArrayList<Object>) VerifyUserGroup(userGroupCode);
			ArrayList<Object> listQuery2 = (ArrayList<Object>) VerifyRoleGroup(userGroupCode);
			
			int len = listQuery.size();
			int len2 = listQuery2.size();
			if (len == 0) {
				result = "success";
			} else {
				Object o = delGroup(userGroupCode);
				if ((len + "").equals(o)) {
					result = "success";
				}
			}
			if (len2 == 0) {
				result2 = "success";
			} else {
				Object o2 = delRole(userGroupCode);
				if ((len2 + "").equals(o2)) {
					result2 = "success";
				}
			}
		}
		if(result=="success"&&result2=="success"){
			return rs;
		}else{
			throw new RuntimeException("删除失败");
		}
	}
	@Override
	public HashMap<String,Object> view(String guid) throws Exception{
		// TODO Auto-generated method stub
		String sql="select * from s_userGroup_info where guid=:guid";
		HashMap<String,Object> tmp=new HashMap<String,Object>();
		tmp.put("guid",guid);
		ArrayList<HashMap<String,Object>> list=querySqlToSQLResultBean(sql,tmp,0,0).getMapList();
		if(list.size() == 1){
			return list.get(0);
		}else{
			throw new Exception("找不到数据");
		}
	}
	/**
	 * 添加用户-用户组中间表
	 * @param map
	 * @param list每条数据
	 * @return
	 * @throws Exception
	 */
	public Object addGroupUser(HashMap<String, String> map,ArrayList<String[]> list) throws Exception {
		// TODO Auto-generated method stub
		String result = "fail";
		int num=0;
		String userGroupCode = (String) map.get("userGroupCode");
		ArrayList<Object> listQuery = (ArrayList<Object>) VerifyUserGroup(userGroupCode);
		int len = listQuery.size();
		if (len == 0) {
			result = "success";
		} else {
			Object o = delGroup(userGroupCode);
			if ((len + "").equals(o)) {
				result = "success";
			}
		}
		if (result == "success") {
			if(list.size()!=0){
				String[] arr = { "guid", "status", "createBy", "createTime","userGroupCode","userAccount"};
				for (String[] s : list) {
					HashMap<String, Object> hashMap = new HashMap<String, Object>();
					for (int i = 0; i < arr.length; i++) {
						hashMap.put(arr[i], s[i]);
					}
					Object o = addGroup(hashMap);
					if ("1".equals(o)) {
						num++;
					} else {
						break;
					}
				}
			}else{
				num=1;
			}
		}else{
			throw new RuntimeException("关联失败！");
		}
		return num;
		
	}
	/**
	 * 用户-用户组中间表的添加
	 * @param map
	 * @return
	 * @throws Exception
	 */
	public Object addGroup(HashMap<String, Object> map) throws Exception {
		// TODO Auto-generated method stub
		SQLInsertBean insertBean=new SQLInsertBean("sr_user_group");
		HashMap<String,Object> columnMap=new HashMap<String,Object>(map);
		insertBean.setColumnMap(columnMap);
		Object result=excuteSqlBean(insertBean);
		return result;
	}
	/**
	 * 用户-用户组中间表删除相关数据
	 * @param userAccount
	 * @return
	 * @throws Exception
	 */
	public Object delGroup(String userGroupCode) throws Exception {
		// TODO Auto-generated method stub
		String sql1 = "delete from sr_user_group where userGroupCode = ?";
		Object o = updateSql(sql1, new Object[] { userGroupCode });
		return o;
	}
	
	/**
	 * 用户组-用户中间表验重
	 * @param userAccount
	 * @return
	 */
	public List VerifyUserGroup(String userGroupCode) {
		// TODO Auto-generated method stub
		Session session=getSessionFactory().getCurrentSession();
		String sql="select guid from sr_user_group where userGroupCode=:userGroupCode and STATUS = 1 ";
		SQLQuery query=session.createSQLQuery(sql);
		query.setString("userGroupCode",userGroupCode);
		return query.list();
	}
	
	/**
	 * 获取未被关联的用户组信息
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String, Object>> getGroupUser(String userGroupCode) throws Exception {
		Session session = getSessionFactory().getCurrentSession();
		String sqlStaff = "select userAccount from sr_user_group where 1=1 and status=1 and userGroupCode='"+userGroupCode+"'";
		// ArrayList<String> listStaff = (ArrayList<String>)querySql(sqlStaff);
		SQLQuery query = session.createSQLQuery(sqlStaff);
		ArrayList<String> listStaff = (ArrayList<String>) query.list();
		String ucode = "";
		String u = "";
		for (int i = 0; i < listStaff.size(); i++) {
			if (i == listStaff.size() - 1) {
				u = "'" + (String) listStaff.get(i) + "'";
			} else {
				u = "'" + (String) listStaff.get(i) + "'" + ",";
			}
			ucode += u;
		}
		String sql = "";
		if ("".equals(ucode)) {
			sql = "select userAccount,userName from s_users_info where 1=1 and status=1 and userType='1' ";
		} else {
			sql = "select userAccount,userName from s_users_info where 1=1 and status=1 and userType='1' and userAccount not in("+ ucode + ")";
		}
		return querySqlToSQLResultBean(sql).getMapList();
	}
	/**
	 * 该成员已关联的用户
	 * @param userAccount
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String, Object>> getUserTeamGroup(String userGroupCode) throws Exception {
		// TODO Auto-generated method stub
		String sql = "select s.userAccount,b.userName from sr_user_group s  left join s_users_info b on  b.userAccount=s.userAccount  where 1=1 and s.status=1 and  s.userGroupCode='"+userGroupCode+"'";
		return querySqlToSQLResultBean(sql).getMapList();
		
	}
	
	/**
	 * 用户组-角色的添加
	 * @param map
	 * @param list
	 * @return
	 * @throws Exception
	 */
	public Object addGroupRole(HashMap<String, String> map,ArrayList<String[]> list) throws Exception {
		// TODO Auto-generated method stub
		String result = "fail";
		int num=0;
		String userGroupCode = (String) map.get("userGroupCode");
		ArrayList<Object> listQuery = (ArrayList<Object>) VerifyRoleGroup(userGroupCode);
		int len = listQuery.size();
		if (len == 0) {
			result = "success";
		} else {
			Object o = delRole(userGroupCode);
			if ((len + "").equals(o)) {
				result = "success";
			}
		}
		if (result == "success") {
			if(list.size()!=0){
				String[] arr = { "guid", "status", "createBy", "createTime","userGroupCode","roleCode" };
				for (String[] s : list) {
					HashMap<String, Object> hashMap = new HashMap<String, Object>();
					for (int i = 0; i < arr.length; i++) {
						hashMap.put(arr[i], s[i]);
					}
					Object o = addRole(hashMap);
					if ("1".equals(o)) {
						num++;
					} else {
						break;
					}
				}
			}else{
				num=1;
			}
			
		}else{
			throw new RuntimeException("设置失败！");
		}
		return num;
		
	}
	/**
	 * 用户组-角色中间表验重
	 * @param userAccount
	 * @return
	 */
	public List VerifyRoleGroup(String userGroupCode) {
		// TODO Auto-generated method stub
		Session session=getSessionFactory().getCurrentSession();
		String sql="select guid from sr_userGroup_role where userGroupCode=:userGroupCode and STATUS = 1 ";
		SQLQuery query=session.createSQLQuery(sql);
		query.setString("userGroupCode",userGroupCode);
		return query.list();
	}
	/**
	 * 用户组-角色中间表删除相关数据
	 * @param userAccount
	 * @return
	 * @throws Exception
	 */
	public Object delRole(String userGroupCode) throws Exception {
		// TODO Auto-generated method stub
		String sql1 = "delete from sr_userGroup_role where userGroupCode = ?";
		Object o = updateSql(sql1, new Object[] { userGroupCode });
		return o;
	}
	/**
	 * 用户组-角色中间表的添加
	 * @param map
	 * @return
	 * @throws Exception
	 */
	public Object addRole(HashMap<String, Object> map) throws Exception {
		// TODO Auto-generated method stub
		SQLInsertBean insertBean=new SQLInsertBean("sr_userGroup_role");
		HashMap<String,Object> columnMap=new HashMap<String,Object>(map);
		insertBean.setColumnMap(columnMap);
		Object resulto=excuteSqlBean(insertBean);
		return resulto;
	}
	/**
	 * 该成员已关联的角色
	 * @param userAccount
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String, Object>> getRoleTeamGroup(String userGroupCode) throws Exception {
		// TODO Auto-generated method stub
		String sql = "select s.roleCode,b.roleName from sr_userGroup_role s left join s_role_info b on b.roleCode=s.roleCode  where 1=1 and s.status=1 and s.userGroupCode='"+userGroupCode+"'";
		return querySqlToSQLResultBean(sql).getMapList();
		
	}
	/**
	 * 获取未被关联的角色信息
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String, Object>> getGroupRole(String userGroupCode) throws Exception {
		Session session = getSessionFactory().getCurrentSession();
		String sqlStaff = "select roleCode from sr_userGroup_role where 1=1 and status=1 and userGroupCode='"+userGroupCode+"'";
		// ArrayList<String> listStaff = (ArrayList<String>)querySql(sqlStaff);
		SQLQuery query = session.createSQLQuery(sqlStaff);
		ArrayList<String> listStaff = (ArrayList<String>) query.list();
		String ucode = "";
		String u = "";
		for (int i = 0; i < listStaff.size(); i++) {
			if (i == listStaff.size() - 1) {
				u = "'" + (String) listStaff.get(i) + "'";
			} else {
				u = "'" + (String) listStaff.get(i) + "'" + ",";
			}
			ucode += u;
		}
		String sql = "";
		if ("".equals(ucode)) {
			sql = "select roleCode,roleName from s_role_info where 1=1 and status=1 ";
		} else {
			sql = "select roleCode,roleName from s_role_info where 1=1 and status=1 and roleCode not in("+ ucode + ")";
		}
		return querySqlToSQLResultBean(sql).getMapList();
	}
	
	
	
	
}
